Imports DAL
Imports MyClasses
Imports System.Data.SqlClient

'@20071123 : Add New Code
Public Class DBankMargin
    Public Shared Function GetCompanyDetails(Optional ByVal Amount = False) As DataTable
        Dim strsql As String = ""
        Try
            If Amount = True Then
                strsql = " SELECT MManufacturer.MfctrName," & _
                         " isnull(SUM((isnull(TStock.Quantity,0)/tstock.conversionfactor) * isnull(TStock.PurchasePrice,0)),0)AS Amount," & _
                         " '0' AS 'Set Margin',isnull(SUM((isnull(TStock.Quantity,0)/tstock.conversionfactor) * isnull(TStock.PurchasePrice,0)),0)AS 'New Amount'" & _
                         " FROM MpItem" & _
                         " LEFT Outer Join MManufacturer ON MManufacturer.MfctrID=MpItem.CompanyID" & _
                         " LEFT Outer Join TStock ON TStock.ItemID=MpItem.ItemID" & _
                         " GROUP BY MpItem.CompanyID, MManufacturer.MfctrName" & _
                         " HAVING ISNULL(SUM((isnull(TStock.Quantity,0)/tstock.conversionfactor) * isnull(TStock.PurchasePrice,0)),0) > 0 " & _
                         " ORDER BY MManufacturer.MfctrName"
            Else
                strsql = " SELECT MManufacturer.MfctrName," & _
                         " isnull(SUM((isnull(TStock.Quantity,0)/tstock.conversionfactor) * isnull(TStock.PurchasePrice,0)),0)AS Amount," & _
                         " '0' AS 'Set Margin',isnull(SUM((isnull(TStock.Quantity,0)/tstock.conversionfactor) * isnull(TStock.PurchasePrice,0)),0)AS 'New Amount'" & _
                         " FROM MpItem" & _
                         " LEFT Outer Join MManufacturer ON MManufacturer.MfctrID=MpItem.CompanyID" & _
                         " LEFT Outer Join TStock ON TStock.ItemID=MpItem.ItemID" & _
                         " GROUP BY MpItem.CompanyID, MManufacturer.MfctrName" & _
                         " ORDER BY MManufacturer.MfctrName"
            End If
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DBankMargin", "GetCompanyDetails")
            Return Nothing
        End Try
    End Function
End Class
